package KeTangLian;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

import KeTangLian.DBManager;
import KeTangLian.QueryDataResult;

public class ManageStudent {
	//储存学生
    StudentBean[] sBeans=new StudentBean[50];
    ArrayList<StudentBean> saArrayList=new ArrayList<StudentBean>();
    Scanner scanner=new Scanner(System.in);
    
	
	//查询
	public void showStudent() {
		String sqlString="select * from student";
		QueryDataResult rSet1=DBManager.excuteQuery(sqlString);
		System.out.println("学号\t姓名\t性别");
			if (rSet1!=null) {
				try {
					while (rSet1.rs.next()) {
						System.out.println(rSet1.rs.getInt("id")+"\t"+rSet1.rs.getString("name")
								+"\t"+rSet1.rs.getString("sex"));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
	}
	
	public void showStudent1() {
		System.out.println("性别");
	    String sexString=scanner.next();
		String sqlString="select * from student where sex='"+sexString+"'";
		QueryDataResult rSet1=DBManager.excuteQuery(sqlString);
		System.out.println("学号\t姓名\t性别");
			if (rSet1!=null) {
				try {
					while (rSet1.rs.next()) {
						System.out.println(rSet1.rs.getInt("id")+"\t"+rSet1.rs.getString("name")
								+"\t"+rSet1.rs.getString("sex"));
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
	}
	
	//删
	public void deleteStudent() {
		System.out.println("请输入要删除的学生id");
		int id=scanner.nextInt();
		String sqlString="DELETE FROM student WHERE id='"+id+"'";
		int result=DBManager.excuteUpdate(sqlString);
		System.out.println("删除成功");
	}
	
	public void deleteStudent1() {
		System.out.println("请输入要删除的学生姓名");
		String name=scanner.next();
		String sqlString="DELETE FROM student WHERE id='"+name+"'";
		int result=DBManager.excuteUpdate(sqlString);
		System.out.println("删除成功");
	}
	
	public void deleteStudent2() {
		System.out.println("请输入要删除的学生性别");
		String sexString=scanner.next();
		System.out.println("请输入要删除的学生id");
		int id=scanner.nextInt();
		String sqlString="DELETE FROM student WHERE if (sexString!=null && id!=null) {sex='"+sexString+"' and id='"+id+"'}";
		int result=DBManager.excuteUpdate(sqlString);
		System.out.println(result>0?"删除成功":"删除失败");
	}
	
	//增
	public void addStudent() {
    System.out.println("学生id");
    int id=scanner.nextInt();
    System.out.println("姓名");
    String nameString=scanner.next();
    System.out.println("性别");
    String sexString=scanner.next();
    String sqlString="insert into student values('"+id+"','"+nameString+"','"+sexString+"')";
    int result=DBManager.excuteUpdate(sqlString);
    System.out.println("添加成功");
    }
	//改
	public void setStudent() {
		System.out.println("请输入要改的学生id");
		int id=scanner.nextInt();
		System.out.println("请输入名字");
		String nameString=scanner.next();
		String sqlString="UPDATE student SET name='"+nameString+"' WHERE id='"+id+"'";
		int result=DBManager.excuteUpdate(sqlString);
		System.out.println("修改成功");
    }
}
